package com.library.dao;

import java.sql.*;
import java.util.*;

import com.library.bean.User;
import com.util.jdbc.JDBCUtils;

/*
 * DAO 数据访问对象
 */
public class UserDAO {

	/**
	 * 验证用户登录功能
	 * 
	 * @throws SQLException
	 */
	public static User login(String name, String pwd) throws SQLException {
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		String sql = "select * from user_table where user_name=? and password=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 放入参数
		stmt.setString(1, name);
		stmt.setString(2, pwd);
		// 3 执行sql语句
		ResultSet rs = stmt.executeQuery();// 注意执行的时候不要再传入sql!
		if (rs.next()) {
			int user_id = rs.getInt(1);
			String user_name = rs.getString(2);
			String password = rs.getString(3);
			int user_role = rs.getInt(4);
			int user_status = rs.getInt(5);
			String create_time = rs.getString(6);
			return new User(user_id, user_name, password, user_role, user_status,create_time);
		}
		// 4. 释放资源
		JDBCUtils.destory(rs, stmt, conn);

		return null;

	}


	/**
	 * 列表用户功能
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<User> findUserAll() throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		Statement stmt = conn.createStatement();
		// 3. 执行sql语句
		String sql = "select * from user_table;";
		ResultSet rs = stmt.executeQuery(sql);
		System.out.println("用户id\t用户名\t密码\t用户角色\t用户权限\t创建时间");
		List<User> list = new ArrayList<>();
		while (rs.next()) {
			int user_id = rs.getInt("user_id"); // 如果传入String 通过列名获取
			String user_name = rs.getString("user_name");
			String password = rs.getString("password");
			int user_role = rs.getInt("user_role");
			int user_status = rs.getInt("user_status");
			String create_time = rs.getString("create_time");
			System.out.println(user_id + "\t" + user_name + "\t" + password + "\t  " + user_role + "\t" + user_status+"\t"+create_time);
			list.add(new User(user_id, user_name, password, user_role, user_status,create_time));
		}
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return list;
	}


}
